package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.HourDataDto;
import com.dy.yunying.api.datacenter.vo.HourDataVo;
import com.dy.yunying.api.enums.HourDataKpiEnum;
import com.dy.yunying.biz.config.YunYingProperties;
import com.dy.yunying.biz.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @ClassName HourDataDao
 * @Description todo
 * @Author nieml
 * @Time 2021/6/24 14:51
 * @Version 1.0
 **/
@Component(value = "dcHourDataDao")
@Slf4j
public class HourDataDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	public List<HourDataVo> getHourDataDim(HourDataDto req) {
		StringBuilder sql = getSqlAndName(req);
		log.info("分时数据查询sql/hourData:[{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<HourDataVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<HourDataVo>(HourDataVo.class));
		long end = System.currentTimeMillis();
		log.info("sql : [{}]", "分时数据报表，耗时：" + (end - start) + "毫秒");
		return list;
	}

	public List<HourDataVo> getHourDataAll(HourDataDto req) {
		StringBuilder allSql = getAllSql(req);
		log.info("分时数据汇总查询sql:[{}]", allSql.toString());
		long start = System.currentTimeMillis();
		List<HourDataVo> allList = clickhouseTemplate.query(allSql.toString(), new Object[]{}, new BeanPropertyRowMapper<HourDataVo>(HourDataVo.class));
		long end = System.currentTimeMillis();
		log.info("sql : [{}]", "分时数据报表，耗时：" + (end - start) + "毫秒");
		return allList;
	}

	//组装分时数据汇总的查询sql
	private StringBuilder getAllSql(HourDataDto req) {
		StringBuilder dimSql = getHourDimSql(req);
		//查询汇总的时候，类别设置为空
		StringBuilder kpiSql = getKpiSql(req, "");

		StringBuilder allPayDevcieNumsSql = getAllPayDevcieNumsSql(req, "");

		StringBuilder querySql = new StringBuilder();
		// 等于4 走汇总
		if (req.getCycleType() == 4) {
			if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
				querySql.append(" select  sum(res.payAmount) payAmount  from ");
				querySql.append(allPayDevcieNumsSql);
			}else {
				querySql.append(" select  sum(coalesce(kpi.newRegNums,0)) newRegNums,sum(kpi.newRegPayAmount) newRegPayAmount  from ");
				querySql.append(dimSql);
				querySql.append(" dim ");
				querySql.append(" left join ");
				querySql.append(kpiSql);
				querySql.append(" on cast(dim.hour AS varchar) = kpi.hour ");
			}
		} else {
			querySql.append(" select dim.hour,coalesce(kpi.newRegNums,0) newRegNums,coalesce(kpi.payAmount,0) payAmount,kpi.newRegPayAmount newRegPayAmount from ");
			querySql.append(dimSql);
			querySql.append(" dim ");
			querySql.append(" left join ");
			querySql.append(kpiSql);
			querySql.append(" on cast(dim.hour AS varchar) = kpi.hour ");
		}

		return querySql;
	}

	//分时数据的查询sql 获取名称
	private StringBuilder getSqlAndName(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		StringBuilder sql = getSql(req);
		StringBuilder allPayDevcieNumsSql = getAllPayDevcieNumsSql(req, queryColumn);
		StringBuilder parentChlNameSql = getParentChlNameSql(req);
		if (StringUtils.isBlank(queryColumn)) {
			return sql;
		}
		StringBuilder nameSql = new StringBuilder();
		if (queryColumn.contains("parentchl")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(" select  res.parentchl parentchl,sum(res.payAmount) payAmount, ");
				}else {
					nameSql.append(" select  res.parentchl parentchl,  SUM(res.newRegNums) newRegNums,sum(res.newRegPayAmount) newRegPayAmount, ");
				}
			} else {
				nameSql.append(" select res.*, ");
			}
			nameSql.append(" case when a.parentchlName is null or a.parentchlName = '' then '未知' else a.parentchlName end parentchlName ");
			nameSql.append(" from ");
			if(req.getCycleType() == 4 && HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
				nameSql.append(allPayDevcieNumsSql);
			}else {
				nameSql.append("(" + sql + " ) res ");
			}
			nameSql.append(" left join ");
			nameSql.append(parentChlNameSql);
			nameSql.append(" on res.parentchl = a.parentchl ");
			if (req.getCycleType() == 4) {
				nameSql.append(" group  by res.parentchl,parentchlName ");
			}
			return nameSql;
		}
		if (queryColumn.contains("deptId")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(" select  res.deptId deptId,sum(res.payAmount) payAmount, ");
				}else {
					nameSql.append(" select  res.deptId deptId,  SUM(res.newRegNums) newRegNums,sum(res.newRegPayAmount) newRegPayAmount, ");
				}
			} else {
				nameSql.append(" select res.*, ");
			}
			//nameSql.append(" case when osd.name is null or osd.name = '' then '未知' else osd.name end deptName ");
			nameSql.append(" coalesce(osd.name,'未知') deptName ");
			nameSql.append(" from ");
			if(req.getCycleType() == 4 && HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
				nameSql.append(allPayDevcieNumsSql);
			}else {
				nameSql.append("(" + sql + " ) res ");
			}
			nameSql.append(" LEFT JOIN dim_200_pangu_mysql_sys_dept osd ON res.deptId = osd.dept_id ");
			if (req.getCycleType() == 4) {
				//nameSql.append(" group  by deptId,deptName ");
				nameSql.append(" group  by deptId,name ");
			}
			return nameSql;
		}
		if (queryColumn.contains("investor")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(" select  res.investor investor,sum(res.payAmount) payAmount, ");
				}else {
					nameSql.append(" select  res.investor investor,  SUM(res.newRegNums) newRegNums,sum(res.newRegPayAmount) newRegPayAmount, ");
				}
			} else {
				nameSql.append(" select res.*, ");
			}
			//nameSql.append(" case when osu.real_name is null or osu.real_name = '' then '未知' else osu.real_name end investorName ");
			nameSql.append(" case when osu.real_name is null or osu.real_name = '' then '未知' else osu.real_name end investorName ");
			nameSql.append(" from ");
			if(req.getCycleType() == 4 && HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
				nameSql.append(allPayDevcieNumsSql);
			}else {
				nameSql.append("(" + sql + " ) res ");
			}
			nameSql.append(" LEFT JOIN dim_200_pangu_mysql_sys_user osu ON res.investor = osu.user_id ");
			if (req.getCycleType() == 4) {
				nameSql.append(" group  by investor,real_name ");
			}
			return nameSql;
		}
		if (queryColumn.contains("userGroupId")) {
			// 等于4 走汇总
			if (req.getCycleType() == 4) {
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(" select  res.userGroupId userGroupId,sum(res.payAmount) payAmount, ");
				}else {
					nameSql.append(" select  res.userGroupId userGroupId,  SUM(res.newRegNums) newRegNums,sum(res.newRegPayAmount) newRegPayAmount, ");
				}
			} else {
				nameSql.append(" select res.*, ");
			}
			nameSql.append(" case when osdg.name is null or osdg.name = '' then '未知' else osdg.name end userGroupName ");
			nameSql.append(" from ");
			if(req.getCycleType() == 4 && HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
				nameSql.append(allPayDevcieNumsSql);
			}else {
				nameSql.append("(" + sql + " ) res ");
			}
			nameSql.append(" LEFT JOIN dim_200_pangu_mysql_sys_dept_group osdg ON osdg.id = res.userGroupId ");
			if (req.getCycleType() == 4) {
				nameSql.append(" group  by userGroupId,name ");
			}
			return nameSql;
		}
		if (queryColumn.contains("pgid")) {
			if (req.getCycleType() == 4) {
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(" select  res.pgid pgid,sum(res.payAmount) payAmount ");
				}else {
					nameSql.append(" select   pgid,  SUM(coalesce(newRegNums,0)) newRegNums,  sum(newRegPayAmount) newRegPayAmount  ");
				}
				nameSql.append(" from ");
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(allPayDevcieNumsSql);
					nameSql.append(" group by res.pgid ");
				}else {
					nameSql.append("(" + sql + " )  ");
					nameSql.append(" group by pgid ");
				}
				return nameSql;
			}
		}
		if (queryColumn.contains("gameid")) {
			if (req.getCycleType() == 4) {
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(" select  res.gameid gameid,sum(res.payAmount) payAmount ");
				}else {
					nameSql.append(" select   gameid,  SUM(coalesce(newRegNums,0)) newRegNums, SUM(coalesce(payAmount,0)) payAmount,  sum(newRegPayAmount) newRegPayAmount  ");
				}
				nameSql.append(" from ");
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(allPayDevcieNumsSql);
					nameSql.append(" group by res.gameid ");
				}else {
					nameSql.append("(" + sql + " )  ");
					nameSql.append(" group by gameid ");
				}
				return nameSql;
			}
		}
		if (queryColumn.contains("appchl")) {
			if (req.getCycleType() == 4) {
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(" select  res.appchl appchl,sum(res.payAmount) payAmount ");
				}else {
					nameSql.append(" select   appchl,  SUM(coalesce(newRegNums,0)) newRegNums, SUM(coalesce(payAmount,0)) payAmount,  sum(newRegPayAmount) newRegPayAmount  ");
				}
				nameSql.append(" from ");
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(allPayDevcieNumsSql);
					nameSql.append(" group by res.appchl ");
				}else {
					nameSql.append("(" + sql + " )  ");
					nameSql.append(" group by appchl ");
				}
				return nameSql;
			}
		}
		if (queryColumn.contains("day")) {
			if (req.getCycleType() == 4) {
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(" select  res.day day,sum(res.payAmount) payAmount ");
				}else {
					nameSql.append(" select   day,  SUM(coalesce(newRegNums,0)) newRegNums,SUM(coalesce(payAmount,0)) payAmount, sum(newRegPayAmount) newRegPayAmount  ");
				}
				nameSql.append(" from ");
				if(HourDataKpiEnum.PAYDEVICES.getType().equals(req.getHourDataKpi())){
					nameSql.append(allPayDevcieNumsSql);
					nameSql.append(" group by res.day ");
				}else {
					nameSql.append("(" + sql + " )  ");
					nameSql.append(" group by day ");
				}
				return nameSql;
			}
		}
		return sql;
	}

	//组装分时数据的查询sql
	private StringBuilder getSql(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		StringBuilder dimSql = getDimSql(req);
		StringBuilder kpiSql = getKpiSql(req, queryColumn);
		StringBuilder querySql = new StringBuilder();
		querySql.append(" select dim.*,coalesce(kpi.newRegNums,0) newRegNums,coalesce(kpi.payAmount,0) payAmount,kpi.newRegPayAmount newRegPayAmount from ");
		querySql.append(dimSql);
		querySql.append(" left join ");
		querySql.append(kpiSql);
		querySql.append(" on cast(dim.hour AS varchar) = kpi.hour ");
		if (StringUtils.isNotBlank(queryColumn)) {
			querySql.append(" and dim." + queryColumn + " = kpi." + queryColumn);
		}
		return querySql;
	}

	//获取查询指标sql
	private StringBuilder getKpiSql(HourDataDto req, String queryColumn) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, queryColumn);
		StringBuilder payDevcieNumsSql= getPayDevcieNumsSql(req,queryColumn);
		StringBuilder newRegPaySql = getNewRegPaySql(req, queryColumn);
		querySql.append(" ( ");
		querySql.append(" select");
		querySql.append(" reg.hour hour,");
		if (StringUtils.isNotBlank(queryColumn)) {
			querySql.append(" reg."+queryColumn+" AS "+queryColumn+",");
		}
		querySql.append("reg.newRegNums newRegNums,payDevice.payAmount payAmount,pay.newRegPayAmount newRegPayAmount from");
		querySql.append(newRegSql);
		querySql.append(" left join ");
		querySql.append(payDevcieNumsSql);
		querySql.append(" on reg.hour = payDevice.hour ");
		if (StringUtils.isNotBlank(queryColumn)) {
			querySql.append(" and reg." + queryColumn + " = payDevice." + queryColumn);
		}
		querySql.append(" left join ");
		querySql.append(newRegPaySql);
		querySql.append(" on reg.hour = pay.hour ");
		if (StringUtils.isNotBlank(queryColumn)) {
			querySql.append(" and reg." + queryColumn + " = pay." + queryColumn);
		}
		querySql.append(" ) kpi ");
		return querySql;
	}

	// 获取新增设备(新增设备注册账号的设备数)sql
	private StringBuilder getNewRegSql(HourDataDto req, String queryColumn) {
		Long startTime = req.getRsTime();
		Long endTime = req.getReTime();
		StringBuilder querySql = new StringBuilder();
		StringBuilder selectSql = getSelectSql(queryColumn);
		StringBuilder groupBySql = getGroupBySql(queryColumn);
		querySql.append(" ( ");
		querySql.append(" select ");
		querySql.append(selectSql);
		querySql.append(" coalesce(count(distinct ukid) ,0) newRegNums --新增设备注册数\n ");
		querySql.append(" from( ");

		querySql.append(
				" SELECT\n" +
						" reg.hour hour,\n" +
						" reg.week week,\n" +
						" reg.month month,\n" +
						" reg.year year,\n" +
						"  reg.reg_day  day,\n" +
						"  reg.ad_id adid,\n" +
						"  reg.ad_account advertiserid,\n" +
						"  reg.os os,\n" +
						"  reg.kid kid,\n" +
						"  reg.game_main pgid,\n" +
						"  reg.game_sub gameid,\n" +
						"  reg.chl_main parentchl,\n" +
						"  reg.chl_sub chl,\n" +
						"  reg.chl_base appchl,\n" +
						" coalesce(wpc.manage,0) investor,\n" +
						" coalesce(wpc.real_name,'') investorName,\n" +
						" coalesce(wpc.dept_id,0) deptId,\n" +
						" coalesce(wpc.dept_name,'') deptName,\n" +
						" coalesce(wpc.dept_group_id,0) userGroupId,\n" +
						" coalesce(wpc.name,'') userGroupName,\n" +
						" IF(reg.latest_username != '', reg.latest_username, NULL) ukid\n" +
						" from\n");
		//设备注册表
		querySql.append(" ( select hour,reg_day,week,month,year,kid,collect,game_main,os,game_sub,chl_main,chl_sub,chl_base,ad_id,ad_account,latest_username\n");
		querySql.append(" from ");
		querySql.append(yunYingProperties.getNinetydeviceregtable()).append(" \n");
		querySql.append("  where spread_type = 1 ").append("\n");
		querySql.append(" and reg_day >= ").append(startTime).append("\n");
		querySql.append(" and reg_day <= ").append(endTime).append("\n");
		querySql.append(this.selectDeviceRegCondition(req, " "));
		querySql.append(" ) reg \n ");
//		//账号注册表
//		querySql.append(" left join (select d_kid from  dwd_200_pangu_atrb_account_reg ");
//		querySql.append(" where  ");
//		querySql.append(" d_reg_day = " + date + "");
//		querySql.append(" group by d_kid ) ureg  ");
//		querySql.append(" on reg.kid =ureg.d_kid ");

		//过滤条件：投放人、组别、部门
		querySql.append(" left join (select manage,real_name,parent_code,chncode,dept_id,dept_name,dept_group_id,name ");
		querySql.append(" from  v_dim_200_pangu_channel_user_dept_group where 1=1 ");
		// 账号渠道权限  以及过滤
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			querySql.append(" and manage IN ( -- 管理的账号 \n");
			querySql.append(req.getUserIds());
			if (StringUtils.isNotBlank(req.getInvestorArr())) {
				querySql.append(",").append(req.getInvestorArr());
			}
			querySql.append(" ) \n");
			if (StringUtils.isNotBlank(req.getDeptIdArr())) {
				querySql.append(" and dept_id IN (").append(req.getDeptIdArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getUserGroupIdArr())) {
				querySql.append("   AND dept_group_id IN (").append(req.getUserGroupIdArr()).append(")");
			}
		} else {
			if (StringUtils.isNotBlank(req.getInvestorArr())) {
				querySql.append(" and manage IN (").append(req.getInvestorArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getDeptIdArr())) {
				querySql.append(" and dept_id IN (").append(req.getDeptIdArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getUserGroupIdArr())) {
				querySql.append("   AND dept_group_id IN (").append(req.getUserGroupIdArr()).append(")");
			}
		}
		querySql.append(" ) wpc \n" +
				" on wpc.parent_code = reg.chl_main \n" +
				" AND wpc.chncode = reg.chl_sub \n");
		querySql.append(" ) ");

		//查询条件
		querySql.append(" where 1=1 ");
		querySql.append(this.getWhereCondition(req, ""));
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			querySql.append(" AND \n");
			querySql.append(" investor IN ( -- 管理的账号 \n");
			querySql.append(req.getUserIds());
			querySql.append(" ) \n");
		}
		querySql.append(" group  by \n");
		querySql.append(groupBySql);
		querySql.append(" having count(distinct ukid) <> 0 ");
		querySql.append(" ) reg ");
		return querySql;
	}


	/**
	 * 获取指标：付费设备
	 * */
	private StringBuilder getPayDevcieNumsSql(HourDataDto req, String queryColumn) {
		Long startTime = req.getRsTime();
		Long endTime = req.getReTime();
		StringBuilder querySql = new StringBuilder();
		StringBuilder selectSql = getSelectSql(queryColumn);
		StringBuilder groupBySql = getGroupBySql(queryColumn);
		querySql.append(" ( ");
		querySql.append(" select ");
		querySql.append(selectSql);
		querySql.append(" coalesce(count(DISTINCT kid),0) payAmount --付费设备\n ");
		querySql.append(" from( ");

		querySql.append(
				" SELECT\n" +
						" pay.hour hour,\n" +
						" pay.week week,\n" +
						" pay.month month,\n" +
						" pay.year year,\n" +
						"  pay.pay_day  day,\n" +
						"  pay.ad_id adid,\n" +
						"  pay.ad_account advertiserid,\n" +
						"  pay.os os,\n" +
						"  pay.d_kid kid,\n" +
						"  pay.game_main pgid,\n" +
						"  pay.game_sub gameid,\n" +
						"  pay.chl_main parentchl,\n" +
						"  pay.chl_sub chl,\n" +
						"  pay.chl_base appchl,\n" +
						" coalesce(wpc.manage,0) investor,\n" +
						" coalesce(wpc.real_name,'') investorName,\n" +
						" coalesce(wpc.dept_id,0) deptId,\n" +
						" coalesce(wpc.dept_name,'') deptName,\n" +
						" coalesce(wpc.dept_group_id,0) userGroupId,\n" +
						" coalesce(wpc.name,'') userGroupName\n" +
						" from\n");
		//设备注册表
		querySql.append(" ( select hour,pay_day,week,month,year,d_kid,collect,game_main,os,game_sub,chl_main,chl_sub,chl_base,d_ad_id AS ad_id,d_ad_account AS ad_account\n");
		querySql.append(" from ");
		querySql.append(yunYingProperties.getDeviceregisterRechargetable()).append(" \n");
		querySql.append("  where d_spread_type = 1 ").append("\n");
		querySql.append(" and pay_day >= ").append(startTime).append("\n");
		querySql.append(" and pay_day <= ").append(endTime).append("\n");
		querySql.append(this.selectDeviceRegCondition(req, " "));
		querySql.append(" ) pay \n ");
		//过滤条件：投放人、组别、部门
		querySql.append(" left join (select manage,real_name,parent_code,chncode,dept_id,dept_name,dept_group_id,name ");
		querySql.append(" from  v_dim_200_pangu_channel_user_dept_group where 1=1 ");
		// 账号渠道权限  以及过滤
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			querySql.append(" and manage IN ( -- 管理的账号 \n");
			querySql.append(req.getUserIds());
			if (StringUtils.isNotBlank(req.getInvestorArr())) {
				querySql.append(",").append(req.getInvestorArr());
			}
			querySql.append(" ) \n");
			if (StringUtils.isNotBlank(req.getDeptIdArr())) {
				querySql.append(" and dept_id IN (").append(req.getDeptIdArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getUserGroupIdArr())) {
				querySql.append("   AND dept_group_id IN (").append(req.getUserGroupIdArr()).append(")");
			}
		} else {
			if (StringUtils.isNotBlank(req.getInvestorArr())) {
				querySql.append(" and manage IN (").append(req.getInvestorArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getDeptIdArr())) {
				querySql.append(" and dept_id IN (").append(req.getDeptIdArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getUserGroupIdArr())) {
				querySql.append("   AND dept_group_id IN (").append(req.getUserGroupIdArr()).append(")");
			}
		}
		querySql.append(" ) wpc \n" +
				" on wpc.parent_code = pay.chl_main \n" +
				" AND wpc.chncode = pay.chl_sub \n");
		querySql.append(" ) ");

		//查询条件
		querySql.append(" where 1=1 ");
		querySql.append(this.getWhereCondition(req, ""));
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			querySql.append(" AND \n");
			querySql.append(" investor IN ( -- 管理的账号 \n");
			querySql.append(req.getUserIds());
			querySql.append(" ) \n");
		}
		querySql.append(" group  by \n");
		querySql.append(groupBySql);
		querySql.append(" having count(distinct kid) <> 0 ");
		querySql.append(" ) payDevice ");
		return querySql;
	}


	/**
	 * 获取指标：付费设备汇总去重
	 * */
	private StringBuilder getAllPayDevcieNumsSql(HourDataDto req, String queryColumn) {
		Long startTime = req.getRsTime();
		Long endTime = req.getReTime();
		StringBuilder querySql = new StringBuilder();
		StringBuilder selectSql = getAllSelectSql(queryColumn);
		StringBuilder groupBySql = getAllGroupBySql(queryColumn);
		querySql.append(" ( ");
		querySql.append(" select ");
		querySql.append(selectSql);
		querySql.append(" coalesce(count(DISTINCT kid),0) payAmount --付费设备\n ");
		querySql.append(" from( ");

		querySql.append(
				" SELECT\n" +
						" pay.week week,\n" +
						" pay.month month,\n" +
						" pay.year year,\n" +
						"  pay.pay_day  day,\n" +
						"  pay.ad_id adid,\n" +
						"  pay.ad_account advertiserid,\n" +
						"  pay.os os,\n" +
						"  pay.d_kid kid,\n" +
						"  pay.game_main pgid,\n" +
						"  pay.game_sub gameid,\n" +
						"  pay.chl_main parentchl,\n" +
						"  pay.chl_sub chl,\n" +
						"  pay.chl_base appchl,\n" +
						" coalesce(wpc.manage,0) investor,\n" +
						" coalesce(wpc.real_name,'') investorName,\n" +
						" coalesce(wpc.dept_id,0) deptId,\n" +
						" coalesce(wpc.dept_name,'') deptName,\n" +
						" coalesce(wpc.dept_group_id,0) userGroupId,\n" +
						" coalesce(wpc.name,'') userGroupName\n" +
						" from\n");
		//设备注册表
		querySql.append(" ( select pay_day,week,month,year,d_kid,collect,game_main,os,game_sub,chl_main,chl_sub,chl_base,d_ad_id AS ad_id,d_ad_account AS ad_account\n");
		querySql.append(" from ");
		querySql.append(yunYingProperties.getDeviceregisterRechargetable()).append(" \n");
		querySql.append("  where d_spread_type = 1 ").append("\n");
		querySql.append(" and pay_day >= ").append(startTime).append("\n");
		querySql.append(" and pay_day <= ").append(endTime).append("\n");
		querySql.append(this.selectDeviceRegCondition(req, " "));
		querySql.append(" ) pay \n ");
		//过滤条件：投放人、组别、部门
		querySql.append(" left join (select manage,real_name,parent_code,chncode,dept_id,dept_name,dept_group_id,name ");
		querySql.append(" from  v_dim_200_pangu_channel_user_dept_group where 1=1 ");
		// 账号渠道权限  以及过滤
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			querySql.append(" and manage IN ( -- 管理的账号 \n");
			querySql.append(req.getUserIds());
			if (StringUtils.isNotBlank(req.getInvestorArr())) {
				querySql.append(",").append(req.getInvestorArr());
			}
			querySql.append(" ) \n");
			if (StringUtils.isNotBlank(req.getDeptIdArr())) {
				querySql.append(" and dept_id IN (").append(req.getDeptIdArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getUserGroupIdArr())) {
				querySql.append("   AND dept_group_id IN (").append(req.getUserGroupIdArr()).append(")");
			}
		} else {
			if (StringUtils.isNotBlank(req.getInvestorArr())) {
				querySql.append(" and manage IN (").append(req.getInvestorArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getDeptIdArr())) {
				querySql.append(" and dept_id IN (").append(req.getDeptIdArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getUserGroupIdArr())) {
				querySql.append("   AND dept_group_id IN (").append(req.getUserGroupIdArr()).append(")");
			}
		}
		querySql.append(" ) wpc \n" +
				" on wpc.parent_code = pay.chl_main \n" +
				" AND wpc.chncode = pay.chl_sub \n");
		querySql.append(" ) ");

		//查询条件
		querySql.append(" where 1=1 ");
		querySql.append(this.getWhereCondition(req, ""));
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			querySql.append(" AND \n");
			querySql.append(" investor IN ( -- 管理的账号 \n");
			querySql.append(req.getUserIds());
			querySql.append(" ) \n");
		}
		querySql.append(" group  by \n");
		querySql.append(groupBySql);
		querySql.append(" having count(distinct kid) <> 0 ");
		querySql.append(" ) res ");
		return querySql;
	}




	// 获取新增充值sql
	private StringBuilder getNewRegPaySql(HourDataDto req, String queryColumn) {
		Long startTime = req.getRsTime();
		Long endTime = req.getReTime();
		StringBuilder querySql = new StringBuilder();
		StringBuilder selectSql = getSelectSql(queryColumn);
		StringBuilder groupBySql = getGroupBySql(queryColumn);
		querySql.append(" ( ");
		querySql.append(" select ");
		querySql.append(selectSql);
		querySql.append(" coalesce(sum(newdevicefees),0) newRegPayAmount --新增充值金额\n ");
		querySql.append(" from( ");

		querySql.append(
				" SELECT\n" +
						" hour hour,\n" +
						" reg_day day,\n" +
						" ad_id adid,\n" +
						" ad_account advertiserid,\n" +
						" os os,\n" +
						" game_main pgid,\n" +
						" game_sub gameid,\n" +
						" chl_main parentchl,\n" +
						" chl_sub chl,\n" +
						" chl_base appchl,\n" +
						" wpc.manage investor,\n" +
						" wpc.real_name investorName,\n" +
						" wpc.dept_id deptId,\n" +
						" wpc.name deptName,\n" +
						" coalesce(wpc.dept_group_id,0) userGroupId,\n" +
						" coalesce(wpc.name,'') userGroupName,\n" +
						" coalesce(fee_1*(1 - coalesce(pg.sharing,0)),0) newdevicefees --新增充值金额\n" +
						" from\n");
		//付费表
		querySql.append(" (select fee_1,reg_day,hour,kid,collect,game_main,os,game_sub,chl_main,chl_sub,chl_base,ad_id,ad_account from  ");
		querySql.append(yunYingProperties.getNinetydeviceregtable()).append(" rereg_tmp \n");
		querySql.append("  where spread_type = 1 ").append("\n");
		querySql.append(" and reg_day >= ").append(startTime).append("\n");
		querySql.append(" and reg_day <= ").append(endTime).append("\n");
		querySql.append("  ) reg ");

		//父游戏表 取分成字段
		querySql.append("        LEFT JOIN (select CAST (id as Int16) as id,sharing from dim_200_pangu_mysql_parent_game pg_tmp) pg\n");
		querySql.append("            on reg.game_main = pg.id\n");

		//过滤条件：投放人、组别、部门
		querySql.append(" left join (select manage,real_name,parent_code,chncode,dept_id,dept_name,dept_group_id,name ");
		querySql.append(" from  v_dim_200_pangu_channel_user_dept_group where 1=1 ");
		// 账号渠道权限  以及过滤
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			querySql.append(" and manage IN ( -- 管理的账号 \n");
			querySql.append(req.getUserIds());
			if (StringUtils.isNotBlank(req.getInvestorArr())) {
				querySql.append(",").append(req.getInvestorArr());
			}
			querySql.append(" ) \n");
			if (StringUtils.isNotBlank(req.getDeptIdArr())) {
				querySql.append(" and dept_id IN (").append(req.getDeptIdArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getUserGroupIdArr())) {
				querySql.append("   AND dept_group_id IN (").append(req.getUserGroupIdArr()).append(")");
			}
		} else {
			if (StringUtils.isNotBlank(req.getInvestorArr())) {
				querySql.append(" and manage IN (").append(req.getInvestorArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getDeptIdArr())) {
				querySql.append(" and dept_id IN (").append(req.getDeptIdArr()).append(")");
			}
			if (StringUtils.isNotBlank(req.getUserGroupIdArr())) {
				querySql.append("   AND dept_group_id IN (").append(req.getUserGroupIdArr()).append(")");
			}
		}
		querySql.append(" ) wpc \n" +
				" on wpc.parent_code = reg.chl_main \n" +
				" AND wpc.chncode = reg.chl_sub \n");

		querySql.append(" ) ");
		//查询条件
		querySql.append(" where 1=1 ");
		querySql.append(this.getWhereCondition(req, ""));
		if (String.valueOf(req.getIsSys()) == null || req.getIsSys() != 1) {
			// -- 渠道权限
			querySql.append(" AND \n");
			querySql.append(" investor IN ( -- 管理的账号 \n");
			querySql.append(req.getUserIds());
			querySql.append(" ) \n");
		}
		querySql.append(" group  by \n");
		querySql.append(groupBySql);
		querySql.append(" ) pay ");
		return querySql;
	}

	// 获取维度sql
	private StringBuilder getDimSql(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		StringBuilder querySql = new StringBuilder();
		StringBuilder hourDimSql = getHourDimSql(req);
		//如果未选择类别
		if (StringUtils.isBlank(queryColumn)) {
			querySql.append(hourDimSql);
			querySql.append(" dim ");
			return querySql;
		}
		StringBuilder queryColumnDimSql = getQueryColumnDimSql(req);
		querySql.append(" ( ");
		querySql.append(" select h.hour,qc.* from  ");
		querySql.append(hourDimSql);
		querySql.append(" h ");
		querySql.append(" cross join ");
		querySql.append(queryColumnDimSql);
		querySql.append(" qc ");
		querySql.append(" ) ");
		querySql.append(" dim ");

		return querySql;
	}

	// 获取GroupBySql
	private StringBuilder getGroupBySql(String queryColumn) {
		StringBuilder querySql = new StringBuilder();
		querySql.append("hour");
		if (StringUtils.isBlank(queryColumn)) {
			return querySql;
		}
		querySql.append(",");
		querySql.append(queryColumn);
		return querySql;
	}


	// 获取SelectSql
	private StringBuilder getSelectSql(String queryColumn) {
		StringBuilder querySql = new StringBuilder();
		querySql.append("hour");
		querySql.append(",");
		if (StringUtils.isBlank(queryColumn)) {
			return querySql;
		}
		querySql.append(queryColumn);
		querySql.append(",");
		return querySql;
	}

	// 获取GroupBySql
	private StringBuilder getAllGroupBySql(String queryColumn) {
		StringBuilder querySql = new StringBuilder();
		querySql.append("summery");
		if (StringUtils.isBlank(queryColumn)) {
			return querySql;
		}
		querySql.append(",");
		querySql.append(queryColumn);
		return querySql;
	}


	// 获取SelectSql
	private StringBuilder getAllSelectSql(String queryColumn) {
		StringBuilder querySql = new StringBuilder();
		querySql.append(" '汇总' as summery");
		querySql.append(",");
		if (StringUtils.isBlank(queryColumn)) {
			return querySql;
		}
		querySql.append(queryColumn);
		querySql.append(",");
		return querySql;
	}


	//获取查询的维度sql
	private StringBuilder getQueryColumnDimSql(HourDataDto req) {
		String queryColumn = req.getQueryColumn();
		switch (queryColumn) {
			case "day":
				return getDayDimSql(req);
			case "pgid":
				return getParentGameDimSql(req);
			case "gameid":
				return getSubGameDimSql(req);
			case "parentchl":
				return getParentChlDimSql(req);
			case "appchl":
				return getAppChlDimSql(req);
			case "investor":
				return getInvestorDimSql(req);
			case "deptId":
				return getDeptDimSql(req);
			case "userGroupId":
				return getUserGroupDimSql(req);
			default:
				//todo
				return getParentGameDimSql(req);
		}
	}

	// 获取小时维度sql：每天的小时和当前小时
	private StringBuilder getHourDimSql(HourDataDto req) {
		String newtoday = DateUtils.dateToString(new Date(), DateUtils.YYYYMMDD);// 今天
		StringBuilder dayHour = new StringBuilder();
		String startTime = String.valueOf(req.getRsTime());
		if (startTime.equals(newtoday)) {
			ArrayList<Integer> list = new ArrayList<Integer>();
			Integer hour = new Date().getHours();
			for (int i = 0; i <= hour; i++) {
				list.add(hour - i);
			}
			dayHour.append("  ( SELECT toUInt8(arrayJoin( " + list + ")) AS hour )  \n");
		} else {
			dayHour.append("  ( SELECT toUInt8(arrayJoin([23 , 22 , 21 , 20 , 19 , 18 , 17 , 16 , 15 , 14 , 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0])) AS hour )  \n");
		}
		return dayHour;
	}

	// 日期
	private StringBuilder getDayDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select day from ");
		querySql.append(newRegSql);
		querySql.append(" group by day ) ");
		return querySql;
	}
	// 获取父游戏维度sql:统一用新增设备sql得到类别的个数
	private StringBuilder getParentGameDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select pgid from ");
		querySql.append(newRegSql);
		querySql.append(" group by pgid ) ");
		return querySql;
	}

	// 获取子游戏维度sql:统一用新增设备sql得到类别的个数
	private StringBuilder getSubGameDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select gameid from ");
		querySql.append(newRegSql);
		querySql.append(" group by gameid ) ");
		return querySql;
	}


	// 获取父渠道维度sql
	private StringBuilder getParentChlDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select parentchl from ");
		querySql.append(newRegSql);
		querySql.append(" group by parentchl ) ");
		return querySql;
	}


	// 获取分包渠道维度sql
	private StringBuilder getAppChlDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select appchl from ");
		querySql.append(newRegSql);
		querySql.append(" group by appchl ) ");
		return querySql;
	}


	// 获取部门维度sql
	private StringBuilder getDeptDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select deptId from ");
		querySql.append(newRegSql);
		querySql.append(" group by deptId )   ");
		return querySql;
	}

	// 获取组别维度sql
	private StringBuilder getUserGroupDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select userGroupId from ");
		querySql.append(newRegSql);
		querySql.append(" group by userGroupId )   ");
		return querySql;
	}

	// 获取投放人维度sql
	private StringBuilder getInvestorDimSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		StringBuilder newRegSql = getNewRegSql(req, req.getQueryColumn());
		querySql.append(" ( select investor from ");
		querySql.append(newRegSql);
		querySql.append(" group by investor ) ");
		return querySql;
	}

	//where 条件
	//筛选条件
	public String getWhereCondition(HourDataDto req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();
		//系统
		Integer os = req.getOs();
		//主游戏
		String pgidArr = req.getPgidArr();
		//子游戏
		String gameidArr = req.getGameidArr();
		//主渠道
		String parentchlArr = req.getParentchlArr();
		//分包渠道
		String appchlArr = req.getAppchlArr();
		//部门
		String deptIdArr = req.getDeptIdArr();
		//组别
		String userGroupIdArr = req.getUserGroupIdArr();
		//投放人
		String investorArr = req.getInvestorArr();

		if (StringUtils.isBlank(bieming)) {

			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append("  AND deptId IN (").append(deptIdArr).append(")");
			}

			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append(" AND investor IN (").append(investorArr).append(")");
			}
			if (StringUtils.isNotBlank(userGroupIdArr)) {
				sqlCondition.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
			}

			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and pgid    = " + pgidArr + "");
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and gameid in (" + gameidArr + ")");
				} else {
					sqlCondition.append(" and gameid  = " + gameidArr + "");
				}
			}
			if (Objects.nonNull(os)) {

				sqlCondition.append(" and os  = ").append(os);
			}
			return sqlCondition.toString();
		} else {
			if (StringUtils.isNotBlank(deptIdArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".deptId IN (").append(deptIdArr).append(")");
			}

			if (StringUtils.isNotBlank(investorArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".investor IN (").append(investorArr).append(")");
			}
			if (StringUtils.isNotBlank(userGroupIdArr)) {
				sqlCondition.append(" AND ").append(bieming).append(".userGroupId IN (").append(userGroupIdArr).append(")");
			}
			if (StringUtils.isNotBlank(parentchlArr)) {
				if (parentchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".parentchl in ('" + parentchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".parentchl  = '" + parentchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(appchlArr)) {
				if (appchlArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".appchl in ('" + appchlArr.replaceAll(",", "','") + "')");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".appchl  = '" + appchlArr + "'");
				}
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				if (pgidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".pgid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".pgid    = " + pgidArr + "");
				}
			}
			if (StringUtils.isNotBlank(gameidArr)) {
				if (gameidArr.contains(",")) {
					sqlCondition.append(" and ").append(bieming).append(".gameid in (" + pgidArr + ")");
				} else {
					sqlCondition.append(" and ").append(bieming).append(".gameid    = " + pgidArr + "");
				}
			}
			if (Objects.nonNull(os)) {

				sqlCondition.append(" and ").append(bieming).append(".os  = " + os);
			}
			return sqlCondition.toString();
		}
	}

	//筛选条件  类别指标筛选
	public String selectDeviceRegCondition(HourDataDto req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String appchlArr = req.getAppchlArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		StringBuffer commCondSB = new StringBuffer("");

		if (os != null) {
			commCondSB.append("                     AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append("                     AND game_main in (" + pgidArr + ")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append("                     AND game_sub in (" + gameidArr + ")");
		}
		if (StringUtils.isNotBlank(appchlArr)) {
			commCondSB.append("                     AND chl_base IN ('").append(appchlArr.replaceAll(",", "','")).append("')");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append("                     AND chl_main IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}

		String commCond = commCondSB.toString();
		log.info("commCond : [{}]", commCond);

		return commCondSB.toString();
	}

	// 获取父渠道名称
	private StringBuilder getParentChlNameSql(HourDataDto req) {
		StringBuilder querySql = new StringBuilder();
		querySql.append(" ( SELECT chncode parentchl,chnname parentchlName FROM dim_200_pangu_mysql_wan_promotion_channel_v3 where pid = 0 ) a ");
		return querySql;
	}

}
